package com.mazegame.backend;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UsersDAO {

    /**
     * 查询 users 表中一共有多少用户
     * @return 用户总数
     */
    public static int getTotalUserCount() {
        String sql = "SELECT COUNT(*) AS user_count FROM users";
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            if (rs.next()) {
                return rs.getInt("user_count");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 将用户根据分数由高到低排序，并获取每个用户的 name 和 score 信息
     * @return 用户的 name 和 score 信息列表
     */
    public static List<UserScoreInfo> getUsersSortedByScore() {
        String sql = "SELECT username, score FROM users ORDER BY score DESC";
        List<UserScoreInfo> userScoreList = new ArrayList<>();
        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            while (rs.next()) {
                String username = rs.getString("username");
                int score = rs.getInt("score");
                userScoreList.add(new UserScoreInfo(username, score));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userScoreList;
    }

    /**
     * 用户分数信息类
     */
    public static class UserScoreInfo {
        private String username;
        private int score;

        public UserScoreInfo(String username, int score) {
            this.username = username;
            this.score = score;
        }

        public String getUsername() {
            return username;
        }

        public int getScore() {
            return score;
        }

        @Override
        public String toString() {
            return "UserScoreInfo{" +
                    "username='" + username + '\'' +
                    ", score=" + score +
                    '}';
        }
    }

    /**
     * 根据用户ID查找用户排名（按分数从高到低）
     * @param userId 用户ID
     * @return 用户排名，如果用户不存在或查询失败返回 -1
     */
    public static int getUserRankById(int userId) {
        getUsersSortedByScore();
        String getScoreSql = "SELECT score FROM users WHERE id = ?";
        String countHigherUsersSql = "SELECT COUNT(*) FROM users WHERE score > ?";

        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmtScore = conn.prepareStatement(getScoreSql)) {

            // 第一步：获取用户分数
            pstmtScore.setInt(1, userId);
            ResultSet rs = pstmtScore.executeQuery();

            if (!rs.next()) {
                // 用户不存在
                return -1;
            }

            int userScore = rs.getInt("score");

            // 第二步：统计分数比当前用户高的用户数量
            try (PreparedStatement pstmtCount = conn.prepareStatement(countHigherUsersSql)) {
                pstmtCount.setInt(1, userScore);
                ResultSet rsCount = pstmtCount.executeQuery();

                if (rsCount.next()) {
                    int higherUserCount = rsCount.getInt(1);
                    return higherUserCount + 1; // 排名 = 高于自己的人数 + 1
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return -1; // 查询失败
    }
}
